package com.sheep.opt;

import com.sheep.opt.ThreadUtil;
import com.sheep.opt.OpList;
import com.sheep.opt.OpUniq;
import com.sheep.opt.OpUpdate;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: wnj
 * Date: 2010-11-15
 * Time: 9:55:29
 * To change this template use File | Settings | File Templates.
 */
public class JdbcBaseDao {
    protected static Logger log = Logger.getLogger("stdout");

    protected List queryList(OpList op) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Connection conn = ThreadUtil.currentConnWnj();
            ps = conn.prepareStatement(op.getSql());
            op.setParam(ps);
            op.log(log);
            rs = ps.executeQuery();
            while (rs.next()) {
                op.add(op.parse(rs));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return op.getResult();
    }

    protected Object queryUnique(OpUniq op) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Connection conn = ThreadUtil.currentConnWnj();
            ps = conn.prepareStatement(op.getSql());
            op.setParam(ps);
            op.log(log);
            rs = ps.executeQuery();
            if (rs.next()) {
                op.add(op.parse(rs));
            }
            if (rs.next()) {
                log
                        .error("Non Unique Result Error: wrong sql syntax or database not consistence!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return op.getResult();
    }

    protected long queryLong(OpUniq op) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        long result = -1;
        try {
            Connection conn = ThreadUtil.currentConnWnj();
            ps = conn.prepareStatement(op.getSql());
            op.setParam(ps);
            op.log(log);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getLong(1);
            } else {
                return result;
            }
            if (rs.next()) {
                log
                        .error("Non Unique Result Error: wrong sql syntax or database not consistence!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }
    
    protected int queryInt(OpUniq op) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = -1;
        try {
            Connection conn = ThreadUtil.currentConnWnj();
            ps = conn.prepareStatement(op.getSql());
            op.setParam(ps);
            op.log(log);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            } else {
                return result;
            }
            if (rs.next()) {
                log
                        .error("Non Unique Result Error: wrong sql syntax or database not consistence!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    protected boolean queryExist(OpUniq op) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Connection conn = ThreadUtil.currentConnWnj();
            ps = conn.prepareStatement(op.getSql());
            op.setParam(ps);
            op.log(log);
            rs = ps.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }

    protected long queryId(OpUniq op) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        long result = 0;
        try {
            Connection conn = ThreadUtil.currentConnWnj();
            ps = conn.prepareStatement(op.getSql());
            op.setParam(ps);
            op.log(log);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
            if (rs.next()) {
                log
                        .error("Non Unique Result Error: wrong sql syntax or database not consistence!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

     static String getPreparedSQL(String sql, Object[] params) {
        // 1 如果没有参数，说明是不是动态SQL语句
        int paramNum = 0;
        if (null != params)
            paramNum = params.length;
        if (1 > paramNum)
            return sql;
        // 2 如果有参数，则是动态SQL语句
        StringBuffer returnSQL = new StringBuffer();
        String[] subSQL = sql.split("\\?");
        for (int i = 0; i < paramNum; i++) {
            if (params[i] instanceof java.util.Date) {
                returnSQL.append(subSQL[i]).append(" '").append(
                        new java.sql.Date(((java.util.Date) params[i])
                                .getTime())).append("' ");
            } else {
                returnSQL.append(subSQL[i]).append(" '").append(params[i])
                        .append("' ");
            }
        }

        if (subSQL.length > params.length) {
            returnSQL.append(subSQL[subSQL.length - 1]);
        }
        return returnSQL.toString();
    }

    protected int update(OpUpdate op) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Connection conn = ThreadUtil.currentConnWnj();
            ps = conn.prepareStatement(op.getSql());
            op.setParam(ps);
            op.log(log);
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    protected int insertReturnId(OpUpdate op) {
//        log.error("DEBUG_INFO==================entry insertReturnId");
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Connection conn = ThreadUtil.currentConnWnj();
            ps = conn.prepareStatement(op.getSql());
//            log.error("DEBUG_INFO==================sql="+op.getSql());
            op.setParam(ps);
            op.log(log);
            int count = ps.executeUpdate();
//            log.error("DEBUG_INFO==================insertReturnId="+count);
            if (count > 0) {
                if (ps != null) {
                    try {
                        ps.close();
                    } catch (SQLException e) {
//                        log.error("DEBUG_INFO================== sql error",e);
                        e.printStackTrace();
                    }
                }
                ps = conn.prepareStatement("select last_insert_id();");
                rs = ps.executeQuery();
                if (rs.next()) {
//                    log.error("DEBUG_INFO==================lastid="+rs.getInt(1));
                    return rs.getInt(1);
                } else {
                    return 0;
                }
            } else {
                return 0;
            }
        } catch (Exception e) {
//            log.error("DEBUG_INFO================== sql error",e);
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
//                    log.error("DEBUG_INFO================== sql error",e);
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
//                    log.error("DEBUG_INFO================== sql error",e);
                    e.printStackTrace();
                }
            }
        }
        return 0;
    }
}
